﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SMP.Resource
{
    using Domain;
    using System.Data;
    public class Vaitro_Res : BaseDB
    {
        
        #region Thành viên

        public DataTable GetAll_Thanhvien()
        {
            return ExecuteQuery(@"SELECT Id_Thanhvien, Hoten, Taikhoan, Matkhau, Mota, Date_Create, Guid_Ctu FROM tb_thanhvien ");
        }

        public bool Delete_Thanhvien(long id_thanhvien)
        {
            string sql = string.Format(@"DELETE FROM tb_thanhvien WHERE id_thanhvien = {0}", id_thanhvien);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert_Thanhvien(Vaitro thanhvien)
        {
            string sql = string.Format(@"SET DATEFORMAT dmy; INSERT INTO tb_thanhvien(hoten,taikhoan,matkhau,mota,date_create) VALUES (N'{0}', '{1}', '{2}', N'{3}', '{4}')",
                                                            thanhvien.Hoten, thanhvien.Taikhoan, thanhvien.Matkhau, thanhvien.Mota, thanhvien.Date_Create);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update_Thanhvien(Vaitro thanhvien)
        {
            string sql = string.Format(@"UPDATE tb_thanhvien SET hoten = N'{0}', taikhoan = '{1}', mota = N'{2}' WHERE id_thanhvien = '{3}')",
                                                            thanhvien.Hoten, thanhvien.Taikhoan, thanhvien.Mota, thanhvien.Id_Thanhvien);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }



        #endregion

        
        #region Vai trò

        public DataTable GetAll_Form()
        {
            return ExecuteQuery(@"SELECT Id_Form, Ten_Form, Mota_Form FROM tb_form ");
        }

        public DataTable GetAll_Form_By_Id_Thanhvien(long id_thanhvien)
        {
            string sql = string.Format(@"SELECT Id_Vaitro, Id_Thanhvien, Id_Form, Ten_Form, Mota_Form FROM tb_vaitro WHERE id_thanhvien = {0}", id_thanhvien);
            return ExecuteQuery(sql);
        }

        private object Insert_Vaitro(Vaitro vaitro)
        {
            string sql = string.Format("INSERT INTO tb_vaitro(id_thanhvien, id_form, ten_form, mota_form) VALUES ('{0}', '{1}', '{2}', N'{3}') ",
                                                                    vaitro.Id_Thanhvien, vaitro.Id_Form, vaitro.Ten_Form, vaitro.Mota_Form);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        private object Delete_Vaitro(object id_thanhvien)
        {
            string sql = string.Format(@"DELETE FROM tb_vaitro WHERE id_thanhvien = {0}", id_thanhvien);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update_Collection_Vaitro(DataTable dtb, object id_thanhvien)
        {
            Delete_Vaitro(id_thanhvien);
            foreach (DataRow r in dtb.Rows)
                Insert_Vaitro(GetData_Vaitro(r, id_thanhvien));
            
            return true;
        }

        private Vaitro GetData_Vaitro(DataRow row, object id_thanhvien)
        {
            Vaitro vaitro = new Vaitro();

            vaitro.Id_Thanhvien = id_thanhvien;

            if (row["Id_Form"] + "" != "")
                vaitro.Id_Form = row["Id_Form"];

            if (row["Ten_Form"] + "" != "")
                vaitro.Ten_Form = row["Ten_Form"];

            if (row["Mota_Form"] + "" != "")
                vaitro.Mota_Form = row["Mota_Form"];

            return vaitro;
        }

        #endregion

    }
}
